package com.tanger.common.excel;

import com.aliyun.oss.ServiceException;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Date;


/**
 * @Description excel工具类(有样式)
 * @Author Hchenbin
 * @Date 2022/3/9 9:47
 * @Version 1.0
 */
public class ExcelUtil {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 下载文件
     *
     * @param fileName 文件名称
     * @param classPathResource 下载资源路径
     * @param response         响应对象
     * @throws IOException
     */
    public static void downloadFile(String fileName, ClassPathResource classPathResource, HttpServletResponse response) throws IOException {
        // 初始化流
        InputStream inputStream = null;
        OutputStream outputStream = null;
        Long timestamp = new Date().getTime()/1000;
        try {
            inputStream = classPathResource.getInputStream();
            outputStream = response.getOutputStream();
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + timestamp + ".xlsx");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            byte [] buf =  new  byte [ 1024 ];
            int  len = inputStream.read(buf);
            while (len != - 1 ) {
                outputStream.write(buf, 0 ,len);
                len = inputStream.read(buf);
            }
        } catch (IOException e) {
            throw new ServiceException(e.getMessage());
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
            inputStream.close();

        }
    }

    /**
     * 读取流
     *
     * @param inStream
     * @return 字节数组
     * @throws Exception
     */
    public static byte[] readStream(InputStream inStream) throws Exception {
        ByteArrayOutputStream outSteam = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int len = -1;
        while ((len = inStream.read(buffer)) != -1) {
            outSteam.write(buffer, 0, len);
        }
        outSteam.close();
        inStream.close();
        return outSteam.toByteArray();
    }

    /**
     *
     * 生成Excel表格
     *
     * @param excelTitles 文件内容标题
     * @param title th字段名称
     * @param datas 二维数组   datas[第几行][第几列] 均从0开始
     * @return
     * @throws Exception
     */
    public static InputStream export(String excelTitles, String[] title, Object[][] datas) throws Exception{
        ByteArrayInputStream in = new ByteArrayInputStream(dispose(excelTitles, title, datas));
        in.close();
        return in;
    }


    public static byte[] dispose(String excelTitles, String[] title, Object[][] datas) throws Exception{
        //表格第一行样式
        WritableCellFormat cellFormatTop=getHeaderCellStyleTop();
        //表格头部标题样式
        WritableCellFormat cellFormat=getHeaderCellStyle("tableTop");
        //单元格样式
        WritableCellFormat cellFormatBody=getBodyCellStyle();
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        WritableWorkbook book = Workbook.createWorkbook(os);
        WritableSheet sheet = book.createSheet(excelTitles, 0);

        //设置第二行高度
        sheet.setRowView(1, 600);

        //设置第一行数据新增
        Label labelTop=new Label(0, 0, excelTitles);
        //居中
        labelTop.setCellFormat(cellFormatTop);
        sheet.addCell(labelTop);
        int tdLength = title.length;
        //定义商品table数据
        Label label;

        //设置标题
        for (int i = 0; i < tdLength; i++){
            label = new Label(i, 1, title[i], cellFormat);
            sheet.addCell(label);
            //设置宽度
            sheet.setColumnView(i, 20);
        }

        //填充内容
        for (int i = 0; i < datas.length; i++) {
            for (int j = 0; j < datas[i].length; j++) {
                Object _d = datas[i][j];
                Label labels = new Label(j, i + 2, _d == null? "" : _d.toString(),cellFormatBody);
                sheet.addCell(labels);
            }
        }
        //合并行
        sheet.mergeCells(0, 0, title.length-1, 0);

        // 写入数据并关闭文件
        book.write();
        book.close();
        os.close();

        return os.toByteArray();
    }

    /**
     * @discription 首行标题样式设置
     * @return  首行样式
     */
    private static WritableCellFormat getHeaderCellStyleTop(){

        /*
         * WritableFont.createFont("宋体")：设置字体为宋体
         * 10：设置字体大小
         * WritableFont.BOLD:设置字体加粗（BOLD：加粗     NO_BOLD：不加粗）
         * false：设置非斜体
         * UnderlineStyle.NO_UNDERLINE：没有下划线
         */
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                20,
                WritableFont.BOLD,
                false,
                UnderlineStyle.NO_UNDERLINE);

        WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);
        try {
            //添加字体设置
            headerFormat.setFont(font);
            //设置单元格背景色：表头为白色
            headerFormat.setBackground(Colour.WHITE);
            //整个表格线为粗线、黑色
            headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //表头内容水平居中显示
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
        } catch (final WriteException e) {
            e.printStackTrace();
        }
        return headerFormat;
    }

    /**
     * @discription 表头单元格样式的设定
     * @param operType 类型
     * @return   表头样式
     */
    private static WritableCellFormat getHeaderCellStyle(final String operType){

        /*
         * WritableFont.createFont("宋体")：设置字体为宋体
         * 10：设置字体大小
         * WritableFont.BOLD:设置字体加粗（BOLD：加粗     NO_BOLD：不加粗）
         * false：设置非斜体
         * UnderlineStyle.NO_UNDERLINE：没有下划线
         */
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                10,
                WritableFont.BOLD,
                false,
                UnderlineStyle.NO_UNDERLINE);
        WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);
        try {
            //添加字体设置
            headerFormat.setFont(font);
            //设置单元格背景色：表头为灰色50%
            headerFormat.setBackground(Colour.GREY_50_PERCENT);
            //整个表格线为粗线、黑色
            headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            if(operType.equals("tableTop")){
                //设置单元格背景色：表头为黄色  #9CF
                //headerFormat.setBackground(Colour.GREY_25_PERCENT);
                //表头内容水平居中显示
                headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
                //把垂直对齐方式指定为居中
                headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            }else{
                //设置单元格背景色：表头为黄色  #9CF
                //headerFormat.setBackground(Colour.WHITE);
                headerFormat.setAlignment(jxl.format.Alignment.LEFT);
                //把垂直对齐方式指定为居中
                headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            }

        } catch (final WriteException e) {
            e.printStackTrace();
        }
        return headerFormat;
    }


    /**
     * @discription 表头单元格样式的设定
     * @return  样式
     */
    private static WritableCellFormat getBodyCellStyle(){

        /*
         * WritableFont.createFont("宋体")：设置字体为宋体
         * 10：设置字体大小
         * WritableFont.NO_BOLD:设置字体非加粗（BOLD：加粗     NO_BOLD：不加粗）
         * false：设置非斜体
         * UnderlineStyle.NO_UNDERLINE：没有下划线
         */
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                10,
                WritableFont.NO_BOLD,
                false,
                UnderlineStyle.NO_UNDERLINE);

        WritableCellFormat bodyFormat = new WritableCellFormat(font);
        try {
            //设置单元格背景色：表体为白色
            bodyFormat.setBackground(Colour.WHITE);
            //整个表格线为细线、黑色
            bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
            //表头内容水平居中显示
            bodyFormat.setAlignment(jxl.format.Alignment.CENTRE);
            //把垂直对齐方式指定为居中
            bodyFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            // 自动换行
            bodyFormat.setWrap(true);
        } catch (final WriteException e) {
            e.printStackTrace();
        }
        return bodyFormat;
    }

    /**
     * 下载文件
     *
     * @param fileName 文件名称
     * @param inputStream 输入流
     * @param response         响应对象
     * @throws IOException
     */
    public static void exportExcel(String fileName, InputStream inputStream,HttpServletResponse response) throws IOException {
        // 初始化流
        OutputStream outputStream = null;
        Long timestamp = new Date().getTime()/1000;
        try {
            outputStream = response.getOutputStream();
            response.setCharacterEncoding("utf-8");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + timestamp + ".xls");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            byte [] buf =  new  byte [ 1024 ];
            int  len = inputStream.read(buf);
            while (len != - 1 ) {
                outputStream.write(buf, 0 ,len);
                len = inputStream.read(buf);
            }
        } catch (IOException e) {
            throw new ServiceException(e.getMessage());
        }finally {
            if (outputStream != null) {
                outputStream.close();
            }
            inputStream.close();
        }
    }

}
